Uploaded image for project: 'Jira Cloud'
  1. Jira Cloud
  2. JRACLOUD-68436

JIRA backup imports are failing returning PSQLException exception

    XMLWordPrintable

Details

    Description

      Summary

      JIRA Cloud imports are failing with PSQLException error

      Environment

      Steps to Reproduce

      1. Generate JIRA Backup
      2. Attempt to import in to Cloud instance

      Expected Results

      JIRA data is imported successfully

      Actual Results

      "Error importing data: PSQLException" message is seen in the UI

      Logs show the following:

      ERROR	com.atlassian.jira.internal.rest.AsyncCloudTaskRunner.privacy-safe	Non recoverable error occurred during cloudImport.	Import task failed: [Error importing data: PSQLException]	cloud.atlassian.provisioning.exception.PermanentProvisioningFailureException: Import task failed: [Error importing data: PSQLException] at com.atlassian.jira.internal.rest.CloudBackupManager.startImport(CloudBackupManager.java:97) at com.atlassian.jira.internal.rest.ImportResource.lambda$importArchive$1(ImportResource.java:76) at com.atlassian.jira.internal.rest.AbstractCloudProvisionerResource.lambda$withTenantedWorkContext$0(AbstractCloudProvisionerResource.java:69) at com.atlassian.jira.internal.filter.InternalResource.withWorkContext(InternalResource.java:104) at com.atlassian.jira.internal.rest.AbstractCloudProvisionerResource.withTenantedWorkContext(AbstractCloudProvisionerResource.java:67) at com.atlassian.jira.internal.rest.ImportResource.importArchive(ImportResource.java:75) at com.atlassian.jira.internal.rest.ImportResource.lambda$post$0(ImportResource.java:66) at com.atlassian.jira.internal.rest.AsyncCloudTaskRunner$AsyncCloudProvisionerHelper.wrapTask(AsyncCloudTaskRunner.java:148) at com.atlassian.jira.internal.rest.AsyncCloudTaskRunner$AsyncCloudProvisionerHelper.lambda$runAsync$0(AsyncCloudTaskRunner.java:128) at java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1590) at java.util.concurrent.CompletableFuture$AsyncSupply.exec(CompletableFuture.java:1582) at java.util.concurrent.ForkJoinTask.doExec(ForkJoinTask.java:289) at java.util.concurrent.ForkJoinPool$WorkQueue.runTask(ForkJoinPool.java:1056) at java.util.concurrent.ForkJoinPool.runWorker(ForkJoinPool.java:1692) at java.util.concurrent.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:157)
      
      ERROR	com.atlassian.jira.bc.dataimport.CloudImportService	Error occurred while importing data.	Failed operation: SetOperation[entityName=jira.properties,entityId=1,propertyKey=jira.db.uuid,newType=5,newMapper=TypeMapper[handler=StringPropertyHandler,valueEntity=OSPropertyString]]	com.opensymphony.module.propertyset.PropertyImplementationException: Failed operation: SetOperation[entityName=jira.properties,entityId=1,propertyKey=jira.db.uuid,newType=5,newMapper=TypeMapper[handler=StringPropertyHandler,valueEntity=OSPropertyString]] at com.atlassian.jira.propertyset.CachingOfBizPropertyEntryStore.propEx(CachingOfBizPropertyEntryStore.java:320) at com.atlassian.jira.propertyset.CachingOfBizPropertyEntryStore.retry(CachingOfBizPropertyEntryStore.java:306) at com.atlassian.jira.propertyset.CachingOfBizPropertyEntryStore.setEntry(CachingOfBizPropertyEntryStore.java:150) at com.atlassian.jira.propertyset.CachingOfBizPropertySet.setImpl(CachingOfBizPropertySet.java:182) at com.opensymphony.module.propertyset.AbstractPropertySet.set(AbstractPropertySet.java:502) at com.opensymphony.module.propertyset.AbstractPropertySet.setString(AbstractPropertySet.java:300) at com.atlassian.jira.config.properties.ApplicationPropertiesStore.setString(ApplicationPropertiesStore.java:150) at com.atlassian.jira.config.properties.ApplicationPropertiesImpl.setString(ApplicationPropertiesImpl.java:100) at com.atlassian.jira.lifecycle.InstanceStateStoreImpl.storeId(InstanceStateStoreImpl.java:35) at com.atlassian.jira.bc.dataimport.CloudImportService.performDbImport(CloudImportService.java:211) at com.atlassian.jira.bc.dataimport.CloudImportTaskRunner.performTasksOnImportDb(CloudImportTaskRunner.java:305) at com.atlassian.jira.bc.dataimport.CloudImportTaskRunner.performTasksOnImportDb(CloudImportTaskRunner.java:356) at com.atlassian.jira.bc.dataimport.CloudImportTaskRunner.lambda$null$1(CloudImportTaskRunner.java:167) at com.atlassian.connpool.impl.AbstractConnectionPoolProvider.withPrivatePool(AbstractConnectionPoolProvider.java:134) at com.atlassian.jira.connpool.JiraViburConnectionPoolProvider.withPrivatePool(JiraViburConnectionPoolProvider.java:97) at com.atlassian.jira.bc.dataimport.AbstractBackupTaskRunner.lambda$null$0(AbstractBackupTaskRunner.java:60) at java.util.Optional.map(Optional.java:215) at com.atlassian.jira.bc.dataimport.AbstractBackupTaskRunner.lambda$runWithSeparateContextAsync$1(AbstractBackupTaskRunner.java:59) at java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1590) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:748) Suppressed: com.atlassian.jira.exception.DataAccessException: org.postgresql.util.PSQLException: ERROR: column "pkey" contains null values at com.atlassian.jira.database.DbConnectionImpl.executeRawSql(DbConnectionImpl.java:139) at com.atlassian.jira.database.constraints.AbstractNotNullConstraint.lambda$getCreator$0(AbstractNotNullConstraint.java:31) at com.atlassian.jira.database.SqlCallback.runQuery(SqlCallback.java:23) at com.atlassian.jira.database.SqlCallback.runQuery(SqlCallback.java:13) at com.atlassian.jira.database.SqlCallback.lambda$null$2(SqlCallback.java:41) at java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1374) at java.util.stream.ReferencePipeline$Head.forEach(ReferencePipeline.java:580) at com.atlassian.jira.database.SqlCallback.lambda$sequence$3(SqlCallback.java:41) at com.atlassian.jira.database.DefaultQueryDslAccessor.lambda$execute$1(DefaultQueryDslAccessor.java:57) at com.atlassian.jira.database.DatabaseAccessorImpl.lambda$runInTransaction$0(DatabaseAccessorImpl.java:67) at com.atlassian.jira.database.DatabaseAccessorImpl.executeQuery(DatabaseAccessorImpl.java:36) at com.atlassian.jira.database.DatabaseAccessorImpl.runInTransaction(DatabaseAccessorImpl.java:62) at com.atlassian.jira.database.DefaultQueryDslAccessor.execute(DefaultQueryDslAccessor.java:56) at com.atlassian.jira.database.constraints.DefaultConstraintManager.lambda$runWithoutConstraints$10(DefaultConstraintManager.java:195) at com.atlassian.jira.bc.dataimport.CloudImportService.performDbImport(CloudImportService.java:219) ... 12 more Caused by: org.postgresql.util.PSQLException: ERROR: column "pkey" contains null values at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2477) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2190) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:300) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:428) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:354) at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:301) at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:287) at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:264) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:260) at sun.reflect.GeneratedMethodAccessor6577.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.vibur.dbcp.proxy.AbstractInvocationHandler.targetInvoke(AbstractInvocationHandler.java:145) at org.vibur.dbcp.proxy.StatementInvocationHandler.doProcessExecute(StatementInvocationHandler.java:204) at org.vibur.dbcp.proxy.StatementInvocationHandler.on(StatementInvocationHandler.java:195) at com.atlassian.jira.connpool.JiraViburConnectionPoolProvider.lambda$null$5(JiraViburConnectionPoolProvider.java:149) at org.vibur.dbcp.proxy.StatementInvocationHandler.processExecute(StatementInvocationHandler.java:137) at org.vibur.dbcp.proxy.StatementInvocationHandler.restrictedInvoke(StatementInvocationHandler.java:91) at org.vibur.dbcp.proxy.StatementInvocationHandler.restrictedInvoke(StatementInvocationHandler.java:41) at org.vibur.dbcp.proxy.AbstractInvocationHandler.invoke(AbstractInvocationHandler.java:81) at com.sun.proxy.$Proxy4933.execute(Unknown Source) at com.atlassian.jira.database.DbConnectionImpl.executeRawSql(DbConnectionImpl.java:137) ... 26 more Caused by: com.querydsl.core.QueryException: Caught PSQLException for insert into "public"."propertyentry" ("entity_name", "entity_id", "property_key", "propertytype") values (?, ?, ?, ?) at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50) at com.querydsl.sql.Configuration.translate(Configuration.java:459) at com.querydsl.sql.dml.SQLInsertClause.executeWithKeys(SQLInsertClause.java:408) at com.atlassian.jira.database.IdGeneratingSQLInsertClause.executeWithId(IdGeneratingSQLInsertClause.java:59) at com.atlassian.jira.propertyset.CachingOfBizPropertyEntryStore$SetOperation.insert(CachingOfBizPropertyEntryStore.java:612) at com.atlassian.jira.propertyset.CachingOfBizPropertyEntryStore$SetOperation.upsert(CachingOfBizPropertyEntryStore.java:603) at com.atlassian.jira.propertyset.CachingOfBizPropertyEntryStore.lambda$tx$7(CachingOfBizPropertyEntryStore.java:291) at com.atlassian.jira.database.DefaultQueryDslAccessor$IsolatedConnectionProvider.lambda$executeQuery$0(DefaultQueryDslAccessor.java:116) at com.atlassian.jira.database.DatabaseAccessorImpl.executeQuery(DatabaseAccessorImpl.java:36) at com.atlassian.jira.database.DefaultQueryDslAccessor$IsolatedConnectionProvider.executeQuery(DefaultQueryDslAccessor.java:116) at com.atlassian.jira.propertyset.CachingOfBizPropertyEntryStore.query(CachingOfBizPropertyEntryStore.java:281) at com.atlassian.jira.propertyset.CachingOfBizPropertyEntryStore.tx(CachingOfBizPropertyEntryStore.java:289) at com.atlassian.jira.propertyset.CachingOfBizPropertyEntryStore.access$300(CachingOfBizPropertyEntryStore.java:69) at com.atlassian.jira.propertyset.CachingOfBizPropertyEntryStore$SetOperation.perform(CachingOfBizPropertyEntryStore.java:590) at com.atlassian.jira.propertyset.CachingOfBizPropertyEntryStore.retry(CachingOfBizPropertyEntryStore.java:300) ... 20 more Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "pk_propertyentry" Detail: Key (id)=(10000) already exists. at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2477) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2190) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:300) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:428) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:354) at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:169) at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:136) at sun.reflect.GeneratedMethodAccessor701.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.vibur.dbcp.proxy.AbstractInvocationHandler.targetInvoke(AbstractInvocationHandler.java:145) at org.vibur.dbcp.proxy.StatementInvocationHandler.doProcessExecute(StatementInvocationHandler.java:204) at org.vibur.dbcp.proxy.StatementInvocationHandler.on(StatementInvocationHandler.java:195) at com.atlassian.jira.connpool.JiraViburConnectionPoolProvider.lambda$null$5(JiraViburConnectionPoolProvider.java:149) at org.vibur.dbcp.proxy.StatementInvocationHandler.processExecute(StatementInvocationHandler.java:137) at org.vibur.dbcp.proxy.StatementInvocationHandler.restrictedInvoke(StatementInvocationHandler.java:91) at org.vibur.dbcp.proxy.StatementInvocationHandler.restrictedInvoke(StatementInvocationHandler.java:41) at org.vibur.dbcp.proxy.AbstractInvocationHandler.invoke(AbstractInvocationHandler.java:81) at com.sun.proxy.$Proxy4934.executeUpdate(Unknown Source) at com.querydsl.sql.dml.SQLInsertClause.executeWithKeys(SQLInsertClause.java:368) ... 32 more
      

      Notes

      Most imports that experience this issue seem to be migrating from Server to Cloud.

      Workaround

      Below steps fixed the issue for different customers:

      1. Unzip the backup file
      2. Edit the file named entities.xml and change the 'OSPropertyEntry id="X"' and 'OSPropertyString id="X"' where X equals to the ID pointed in the error, to an unused id, like 9999 for example.
      3. Re-zip the file (make sure you maintain the original structure or the import will fail)
      4. Import the zip file from previous step into JIRA

      Attachments

        Issue Links

          Activity

            People

              edalgliesh Eric Dalgliesh
              hbui Houston B.
              Votes:
              37 Vote for this issue
              Watchers:
              50 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: